package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

import utils.Utility;
import constants.Constants;

public class LoginDao {
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public HashMap<String, String> getUidPwds(){
		HashMap<String, String> result = new HashMap<String, String>();
		String query = "select Email, Password from dbo.DVIP_SYS_USER";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				String uid = rs.getString("Email");
				String pwd = rs.getString("Password");
				result.put(uid, pwd);
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public int getUserIdFromEmail(String email){
		int userId = -1;
		
		String query = "select User_Id from dbo.DVIP_SYS_USER where UPPER(Email) = UPPER(?)";
		
		Connection sqlConnection = null;
		ResultSet  rs = null;
		PreparedStatement pStmt = null;
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			
			pStmt = sqlConnection.prepareStatement(query);
			pStmt.setString(1, email);
			
			rs = pStmt.executeQuery();
			while(rs.next())
			{	
				userId = rs.getInt("User_Id");
			}
			
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		return userId;
	}
	
	public int registerUser(String name, String email, String password) {
		int newUserId = -1;
		
		String maxQuery = "select isnull(max(User_Id),0) + 1 new_user_id from dbo.DVIP_SYS_USER";
		String insertQuery = "insert into dbo.DVIP_SYS_USER (Email, Password, Name, User_Id, Active, User_Type, Timestamp) values (?, ?, ?, ?, ?, ?, ?)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(maxQuery);
			while(rs.next())
			{	
				newUserId = rs.getInt("new_user_id");
			}
			rs.close();
			stmt.close();
			
			java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(new java.util.Date().getTime());
			
			pStmt = sqlConnection.prepareStatement(insertQuery);
			int colIndex = 1;
			pStmt.setString(colIndex++, email);
			pStmt.setString(colIndex++, Utility.encodePassword(password));
			pStmt.setString(colIndex++, name);
			pStmt.setInt(colIndex++, newUserId);
			pStmt.setInt(colIndex++, 1);
			pStmt.setInt(colIndex++, 1);
			pStmt.setTimestamp(colIndex++, sqlTimeStamp);
			
			pStmt.executeUpdate();
			
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			newUserId = -1;
			e.printStackTrace();
		}
		
		return newUserId;
	}
	
	public boolean updatePassword(String email, String password){
		boolean result = false;
		String query = "update dbo.DVIP_SYS_USER set Password = ? where Email = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		int rowsUpdated = 0;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			
			pStmt.setString(colIndex++, Utility.encodePassword(password));
			pStmt.setString(colIndex++, email);
			
			rowsUpdated = pStmt.executeUpdate();
			
			if(rowsUpdated>0){
				result = true;
			}
			
			pStmt.close();
			
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public String getName(int userId) {
		String name = "";
		
		String query = "select Name from dbo.DVIP_SYS_USER where User_Id = ?";
		
		Connection sqlConnection = null;
		ResultSet  rs = null;
		PreparedStatement pStmt = null;
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			
			pStmt = sqlConnection.prepareStatement(query);
			pStmt.setInt(1, userId);
			
			rs = pStmt.executeQuery();
			while(rs.next())
			{	
				name = rs.getString("Name");
			}
			
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		return name;
	}
	
}
